<?php
echo '<pre>';

include('../config.php');
include_once('../api/class.mysqli_database.php');
include('../api/xml2json.php');

function replace_unicode_escape_sequence($match) {
	return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UCS-2BE');
}

$conString = sprintf("server=%s; database=%s; username=%s; password=%s; charset=UTF8;", $mysql_host, $mysql_db, $mysql_user, $mysql_pass);
$db = new MysqliDatabase($conString);
$db->set_charset("utf8");

$src = 'http://www.silverbiology.com/clients/gbif/resources/vocabulary.json';

$srcJson = file_get_contents($src);
$extensions = json_decode($srcJson,true);

$rcdQuery = "SELECT max(recordID) rd FROM `xref`";
$ret = $db->query_one($rcdQuery);
$recordId = is_null($ret) ? 0: $ret->rd;
$tmpId = $recordId;

$lookUpTerms = array();
$lookUpTermsQuery = ' SELECT `recordID`,`term` FROM `terms` ';
$lookUps = $db->query_all($lookUpTermsQuery);
if(is_array($lookUps) && count($lookUps)) {
	foreach($lookUps as $lookUp) {
		$lookUpTerms[$lookUp->term] = $lookUp->recordID;
	}
}

if(is_array($extensions['vocabulary']) && count($extensions['vocabulary'])) {
	foreach($extensions['vocabulary'] as $extension) {

		$xmlStringContents = str_replace('dc:','',@file_get_contents($extension['url']));
		$data = str_replace('@','', @xml2json::transformXmlStringToJson(utf8_decode($xmlStringContents)));
		$data = preg_replace_callback('/\\\\u([0-9a-f]{4})/i', 'replace_unicode_escape_sequence', $data);
		$data = json_decode($data ,true);

		$attributes = $data['thesaurus']['attributes'];
		if(is_array($attributes) && count($attributes)) {
			$code = $attributes['title'];
			$codeQuery = sprintf(" INSERT IGNORE INTO `code` (`code`,`codeAbbreviation`,`namespace`,`rowType`,`description`,`link`,`type`) VALUES  ('%s','%s','%s','%s','%s','%s','vocabulary') "
				, mysql_escape_string($attributes['title'])
				, mysql_escape_string($attributes['name'])
				, mysql_escape_string($attributes['namespace'])
				, mysql_escape_string($attributes['rowType'])
				, mysql_escape_string($attributes['description'])
				, mysql_escape_string($attributes['URI'])
			);
			$db->query($codeQuery);
		}

		if(is_array($data['thesaurus']['concept']) && count($data['thesaurus']['concept'])) {

			foreach($data['thesaurus']['concept'] as $concept) {
				if(!isset($lookUpTerms[$concept['attributes']['identifier']])) {
					$db->query(" INSERT INTO `xref` SET `type` = 'see' ");
					$tmpId = $db->insert_id;

					$recordId = $tmpId;
					$lookUpTerms[$concept['attributes']['identifier']] = $recordId;
					if($concept['attributes']['identifier'] != '') {
						$query = sprintf(" INSERT INTO `terms` (`recordID`,`term`,`termStatus`, `termType`,`modified_date`,`created_date`) VALUES (%d, '%s', 'Primary', '%s', NOW(), NOW()) ", $recordId, mysql_escape_string($concept['attributes']['identifier']),mysql_escape_string($concept['attributes']['group']));
						if($db->query($query)) {
							$termCount++;
						}
					}
				} else {
					$recordId = $lookUpTerms[$concept['attributes']['identifier']];
				}

				# $description = ($concept['attributes']['description'] == '') ? 'No definition given.' : $concept['attributes']['description'];

				$description = $concept['attributes']['description'];
				$required = ($concept['attributes']['required'] == 'true') ? 1 : 0;

				$query = sprintf(" INSERT INTO `definitions` (`recordID`,`definition`,`code`, `namespace`,`qualName`,`termType`,`dataType`,`examples`,`required`,`modified_date`,`created_date`) VALUES (%d,'%s','%s', '%s','%s','%s', '%s','%s', '%s', NOW(), NOW()) "
					, $recordId
					, mysql_escape_string($description)
					, mysql_escape_string($code)
					, mysql_escape_string($concept['attributes']['namespace'])
					, mysql_escape_string($concept['attributes']['URI'])
					, mysql_escape_string($concept['attributes']['group'])
					, mysql_escape_string($concept['attributes']['type'])
					, mysql_escape_string($concept['attributes']['examples'])
					, $required
				);

				if($db->query($query)) {
					$defenitionCount++;
				}

			}
		}

	} # extension foreach
} # extensions

print '<br> Total Terms Inserted: ' . $termCount;
print '<br> Total Defenitions Inserted: ' . $defenitionCount;

?>